DECLARE @NewSourceTables TABLE (VALUE Nvarchar(100))
INSERT INTO @NewSourceTables VALUES ('Item_Special Group Link')
INSERT INTO @NewSourceTables VALUES ('Item Special Groups')

DELETE FROM @NewSourceTables
WHERE VALUE in(
  SELECT SourceTableName FROM [dbo].[LSInsight$SourceTablesMap]
  WHERE [SourceTableName] in (Select * from @NewSourceTables)
  group by SourceTableName)

  DECLARE @value VARCHAR(50)
  DECLARE @RC int

DECLARE db_cursor CURSOR FOR  
SELECT Value FROM @NewSourceTables
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @value   

WHILE @@FETCH_STATUS = 0   
BEGIN   
		PRINT 'Adding: ' + @value
	   EXECUTE @RC = [dbo].[LSInsight$EditSourceTable] 
   @value

       FETCH NEXT FROM db_cursor INTO @value   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DW].[odSpecialGroup]') AND type in (N'U'))
DROP TABLE [DW].[odSpecialGroup]

GO 

IF EXISTS (SELECT*
  FROM sysobjects
  WHERE Id=OBJECT_ID( N'[dbo].[odimSpecialGroup]' )
		AND OBJECTPROPERTY( Id, N'IsProcedure' )=1)
BEGIN
DROP PROCEDURE [dbo].[odimSpecialGroup]
END
GO

/****** Object:  Table [DW].[dSpecialGroup]    Script Date: 03/02/2022 11:26:10 ******/


CREATE TABLE [DW].[odSpecialGroup](
	[SK_Company] [int] NULL,
	[SK_Item] [int] NULL,
	[Special Group Code] [nvarchar](20) NULL,
	[Description] [nvarchar](30) NULL,
	[RowID] [bigint] NULL,
	[Batchdate] [datetime] NOT NULL
) ON [PRIMARY]





GO

CREATE PROCEDURE [dbo].[odimSpecialGroup]

AS
BEGIN

	WITH tDItem
	AS
	(SELECT
			[i].[SK_Item]
		   ,[dCOM].[CompanyPrefix]
		   ,[i].[No]
		FROM [DW].[dItem] [i]
		LEFT JOIN [DW].[dCompany] dCOM
			ON [i].[Company] = [dCOM].[SK_Company])

	MERGE [DW].[odSpecialGroup] AS TARGET USING (SELECT
			dCOM.[SK_Company]
		   ,tITM.[SK_Item]
		   ,sIGL.[Special Group Code]
		   ,sISG.[Description]
		   ,MAX(sIGL.[bigint_timestamp]) AS [RowID]
		   ,GETUTCDATE() AS [Batchdate]
		FROM [stg$Item_Special Group Link] sIGL
		LEFT OUTER JOIN [tDItem] tITM
			ON sIGL.[CompanyPrefix] = [tITM].[CompanyPrefix]
			AND sIGL.[Item No_] = [tITM].[No]
		LEFT OUTER JOIN [stg$Item Special Groups] sISG
			ON sIGL.[CompanyPrefix] = sISG.[CompanyPrefix]
			AND sIGL.[Special Group Code] = sISG.[Code]
		LEFT JOIN [DW].[dCompany] dCOM
			ON sIGL.[CompanyPrefix] = dCOM.[CompanyPrefix]
		GROUP BY dCOM.[SK_Company]
				,tITM.[SK_Item]
				,sIGL.[Special Group Code]
				,sISG.[Description]) AS Source

	ON TARGET.[SK_Company] = Source.[SK_Company]
		AND TARGET.[SK_Item] = Source.[SK_Item]
		AND TARGET.[Special Group Code] = Source.[Special Group Code]

	WHEN MATCHED
		THEN UPDATE
			SET [Description] = Source.[Description]
			   ,[SK_Company] = Source.[SK_Company]
			   ,[Batchdate] = Source.[Batchdate]
			   ,[RowID] = Source.[RowID]

	WHEN NOT MATCHED BY TARGET
		THEN INSERT ([SK_Company]
			, [SK_Item]
			, [Special Group Code]
			, [Description]
			, [RowID]
			, [Batchdate])
				VALUES (Source.[SK_Company], Source.[SK_Item], Source.[Special Group Code], Source.[Description], Source.[RowID], Source.[Batchdate])

	;
	;
END


	SELECT
		'RowCount' = @@rowcount
GO


